from sqlalchemy import Boolean, Column, ForeignKey, Integer, String, Enum as SQLAlchemyEnum, DateTime, Float, Table, JSON, Text, CheckConstraint
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from datetime import datetime
from typing import List, Optional
from app.db import Base
from app.schemas import Gender, SleepingHabit, Cleanliness

# 学生与兴趣爱好的多对多关系表
student_interest_association = Table(
    'student_interest_association',
    Base.metadata,
    Column('student_id', Integer, ForeignKey('students.id')),
    Column('interest_id', Integer, ForeignKey('interests.id'))
)

class Student(Base):
    __tablename__ = "students"
    
    id = Column(Integer, primary_key=True, index=True)
    student_id = Column(String, unique=True, index=True)
    name = Column(String)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    gender = Column(SQLAlchemyEnum(Gender))
    grade = Column(Integer, nullable=True, default=None)
    major = Column(String, nullable=True)
    is_active = Column(Boolean, default=True)
    
    # 偏好设置
    sleeping_habit = Column(SQLAlchemyEnum(SleepingHabit), nullable=True)
    cleanliness = Column(SQLAlchemyEnum(Cleanliness), nullable=True)
    
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())
    
    # 关系
    interests = relationship("Interest", secondary="student_interest_association")
    preferences = relationship("Preference", back_populates="student", foreign_keys="[Preference.student_id]")
    room_assignment = relationship("RoomAssignment", back_populates="student", uselist=False)
    room_id = Column(Integer, ForeignKey("rooms.id"), nullable=True)
    room = relationship("Room", back_populates="students")

class Interest(Base):
    __tablename__ = "interests"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True)
    description = Column(String, nullable=True)
    
    # 关系
    students = relationship("Student", secondary="student_interest_association")

class Preference(Base):
    __tablename__ = "preferences"
    
    id = Column(Integer, primary_key=True, index=True)
    student_id = Column(Integer, ForeignKey("students.id"))
    preferred_student_id = Column(Integer, ForeignKey("students.id"), nullable=True)
    preference_level = Column(Integer, default=1)  # 1-5
    interest_tags = Column(JSON, nullable=True)  # 存储为JSON数组
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # 关系
    student = relationship("Student", back_populates="preferences", foreign_keys=[student_id])
    preferred_student = relationship("Student", foreign_keys=[preferred_student_id])

class Building(Base):
    __tablename__ = "buildings"
    
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True)
    floors = Column(Integer)
    rooms_per_floor = Column(Integer)
    gender = Column(SQLAlchemyEnum(Gender))
    
    # 关系
    rooms = relationship("Room", back_populates="building")

class Room(Base):
    __tablename__ = "rooms"
    
    id = Column(Integer, primary_key=True, index=True)
    building_id = Column(Integer, ForeignKey("buildings.id"))
    floor = Column(Integer)
    room_number = Column(String, unique=True)
    capacity = Column(Integer)
    current_occupancy = Column(Integer, default=0)
    
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关系
    building = relationship("Building", back_populates="rooms")
    assignments = relationship("RoomAssignment", back_populates="room")
    students = relationship("Student", back_populates="room")

class RoomAssignment(Base):
    __tablename__ = "room_assignments"
    
    id = Column(Integer, primary_key=True, index=True)
    student_id = Column(Integer, ForeignKey("students.id"))
    room_id = Column(Integer, ForeignKey("rooms.id"))
    is_active = Column(Boolean, default=True)
    assigned_at = Column(DateTime(timezone=True), server_default=func.now())
    moved_out_at = Column(DateTime(timezone=True), nullable=True)
    
    # 关系
    student = relationship("Student", back_populates="room_assignment")
    room = relationship("Room", back_populates="assignments")

class Admin(Base):
    __tablename__ = "admins"
    
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    email = Column(String, unique=True)
    hashed_password = Column(String)
    is_super_admin = Column(Boolean, default=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    last_login = Column(DateTime(timezone=True), nullable=True)

class SwapRequest(Base):
    __tablename__ = "swap_requests"
    
    id = Column(Integer, primary_key=True, index=True)
    requester_id = Column(Integer, ForeignKey("students.id"))
    target_id = Column(Integer, ForeignKey("students.id"))
    status = Column(String, default="pending")  # pending, approved, rejected
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    processed_at = Column(DateTime(timezone=True), nullable=True)
    processed_by = Column(Integer, ForeignKey("admins.id"), nullable=True) 